from __future__ import annotations

import logging
import os
from dataclasses import dataclass
from datetime import datetime
from typing import List

import mysql.connector
from mysql.connector import Error
from contextlib import contextmanager

logging.basicConfig(level=logging.INFO, format="[%(levelname)s] %(message)s")

logger = logging.getLogger(__name__)


@dataclass(frozen=True)
class DBConfig:
    host: str = os.getenv("TIMER_DB_HOST", "172.22.151.137")
    port: int = int(os.getenv("TIMER_DB_PORT", "3306"))
    user: str = os.getenv("TIMER_DB_USER", "root")
    password: str = os.getenv("TIMER_DB_PASSWORD", "123456")
    database: str = os.getenv("TIMER_DB_NAME", "timer_db")


DB_CONFIG = DBConfig()


@dataclass(frozen=True)
class TimerRecord:
    id: int
    hours: int
    minutes: int
    seconds: int
    created_at: datetime  # naive datetime in DB server local time


@contextmanager
def db_conn():
    conn = None
    try:
        conn = mysql.connector.connect(
            host=DB_CONFIG.host,
            port=DB_CONFIG.port,
            user=DB_CONFIG.user,
            password=DB_CONFIG.password,
            database=DB_CONFIG.database,
            autocommit=False,
        )
        yield conn
        conn.commit()
    except Error as e:
        if conn:
            conn.rollback()
        logger.error(f"数据库操作失败: {e}")
        raise
    finally:
        if conn and conn.is_connected():
            conn.close()


def init_db() -> None:
    try:
        with db_conn() as conn:
            with conn.cursor() as cursor:
                cursor.execute(
                    """
                    CREATE TABLE IF NOT EXISTS timers (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        hours INT NOT NULL,
                        minutes INT NOT NULL,
                        seconds INT NOT NULL,
                        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                    )
                    """
                )
    except Error:
        # 已记录并抛出，不再重复
        pass


def save_timer(hours: int, minutes: int, seconds: int) -> None:
    with db_conn() as conn:
        with conn.cursor() as cursor:
            cursor.execute(
                """
                INSERT INTO timers (hours, minutes, seconds)
                VALUES (%s, %s, %s)
                """,
                (int(hours), int(minutes), int(seconds)),
            )


def get_timers() -> List[TimerRecord]:
    try:
        with db_conn() as conn:
            with conn.cursor() as cursor:
                cursor.execute(
                    """
                    SELECT id, hours, minutes, seconds, created_at
                    FROM timers
                    ORDER BY created_at DESC
                    """
                )
                rows = cursor.fetchall() or []
                return [
                    TimerRecord(
                        id=int(r[0]),
                        hours=int(r[1]),
                        minutes=int(r[2]),
                        seconds=int(r[3]),
                        created_at=r[4],
                    )
                    for r in rows
                ]
    except Error:
        logger.exception("查询失败")
        return []


def clear_timers() -> None:
    with db_conn() as conn:
        with conn.cursor() as cursor:
            cursor.execute("TRUNCATE TABLE timers")


def delete_timer_by_id(timer_id: int) -> None:
    with db_conn() as conn:
        with conn.cursor() as cursor:
            cursor.execute("DELETE FROM timers WHERE id = %s", (int(timer_id),))
